<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>MySQL 相关总结 | 寒冷如铁</title>
    <meta name="generator" content="VuePress 1.8.2">
    <link rel="icon" href="/blog/favicon.ico">
    <script language="javascript" type="text/javascript" src="https://cdn.bootcdn.net/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    <script language="javascript" type="text/javascript" src="/blog/js/MouseClickEffect.js"></script>
    <meta name="description" content="好奇就尝试，喜欢就坚持！">
    <meta name="viewport" content="width=device-width,initial-scale=1,user-scalable=no">
    
    <link rel="preload" href="/blog/assets/css/0.styles.01ca31a9.css" as="style"><link rel="preload" href="/blog/assets/js/app.0c46c7d8.js" as="script"><link rel="preload" href="/blog/assets/js/3.5679fe00.js" as="script"><link rel="preload" href="/blog/assets/js/1.8cdd2163.js" as="script"><link rel="preload" href="/blog/assets/js/17.d65c6ccd.js" as="script"><link rel="prefetch" href="/blog/assets/js/10.0b6ffd8d.js"><link rel="prefetch" href="/blog/assets/js/11.d27e4c34.js"><link rel="prefetch" href="/blog/assets/js/12.ba5bd991.js"><link rel="prefetch" href="/blog/assets/js/13.ab5bead7.js"><link rel="prefetch" href="/blog/assets/js/14.124a0dc3.js"><link rel="prefetch" href="/blog/assets/js/15.ac521310.js"><link rel="prefetch" href="/blog/assets/js/16.b32290db.js"><link rel="prefetch" href="/blog/assets/js/18.0a7a10ef.js"><link rel="prefetch" href="/blog/assets/js/19.e0e5e91a.js"><link rel="prefetch" href="/blog/assets/js/20.bcc0e382.js"><link rel="prefetch" href="/blog/assets/js/21.59edea58.js"><link rel="prefetch" href="/blog/assets/js/22.8239cecf.js"><link rel="prefetch" href="/blog/assets/js/23.48516c82.js"><link rel="prefetch" href="/blog/assets/js/24.4e1a046f.js"><link rel="prefetch" href="/blog/assets/js/25.56c0a228.js"><link rel="prefetch" href="/blog/assets/js/26.fb764e02.js"><link rel="prefetch" href="/blog/assets/js/27.f96c7435.js"><link rel="prefetch" href="/blog/assets/js/28.31139fa0.js"><link rel="prefetch" href="/blog/assets/js/29.fa31f05f.js"><link rel="prefetch" href="/blog/assets/js/30.49c791b1.js"><link rel="prefetch" href="/blog/assets/js/31.68660a8e.js"><link rel="prefetch" href="/blog/assets/js/32.4e07e3b0.js"><link rel="prefetch" href="/blog/assets/js/33.e08324f8.js"><link rel="prefetch" href="/blog/assets/js/34.9153a266.js"><link rel="prefetch" href="/blog/assets/js/35.49d3bb37.js"><link rel="prefetch" href="/blog/assets/js/36.5a46a47a.js"><link rel="prefetch" href="/blog/assets/js/37.2403de70.js"><link rel="prefetch" href="/blog/assets/js/38.794a3bde.js"><link rel="prefetch" href="/blog/assets/js/39.185570eb.js"><link rel="prefetch" href="/blog/assets/js/4.a5921925.js"><link rel="prefetch" href="/blog/assets/js/40.b5ddc5b2.js"><link rel="prefetch" href="/blog/assets/js/41.81f131ab.js"><link rel="prefetch" href="/blog/assets/js/42.474cb45b.js"><link rel="prefetch" href="/blog/assets/js/43.fe269b94.js"><link rel="prefetch" href="/blog/assets/js/44.123909bf.js"><link rel="prefetch" href="/blog/assets/js/45.85e17df3.js"><link rel="prefetch" href="/blog/assets/js/46.96eb5367.js"><link rel="prefetch" href="/blog/assets/js/47.2c06ceb7.js"><link rel="prefetch" href="/blog/assets/js/48.dcaa5d75.js"><link rel="prefetch" href="/blog/assets/js/49.47475370.js"><link rel="prefetch" href="/blog/assets/js/5.a6bfa893.js"><link rel="prefetch" href="/blog/assets/js/50.29aa41a9.js"><link rel="prefetch" href="/blog/assets/js/51.b049cb19.js"><link rel="prefetch" href="/blog/assets/js/52.9f16a8d1.js"><link rel="prefetch" href="/blog/assets/js/53.9ccedc0d.js"><link rel="prefetch" href="/blog/assets/js/54.c16fde39.js"><link rel="prefetch" href="/blog/assets/js/55.3d2e78cc.js"><link rel="prefetch" href="/blog/assets/js/56.14b1dddb.js"><link rel="prefetch" href="/blog/assets/js/57.a48eb0da.js"><link rel="prefetch" href="/blog/assets/js/58.db985774.js"><link rel="prefetch" href="/blog/assets/js/59.dbb67461.js"><link rel="prefetch" href="/blog/assets/js/6.d5f7873e.js"><link rel="prefetch" href="/blog/assets/js/60.fd9edd5c.js"><link rel="prefetch" href="/blog/assets/js/61.c87eaa37.js"><link rel="prefetch" href="/blog/assets/js/62.09bd4303.js"><link rel="prefetch" href="/blog/assets/js/63.c3a4cb82.js"><link rel="prefetch" href="/blog/assets/js/64.1e623e6b.js"><link rel="prefetch" href="/blog/assets/js/65.5ee98079.js"><link rel="prefetch" href="/blog/assets/js/66.875fdeb3.js"><link rel="prefetch" href="/blog/assets/js/67.52e4feee.js"><link rel="prefetch" href="/blog/assets/js/68.09bb6522.js"><link rel="prefetch" href="/blog/assets/js/69.e5cc1032.js"><link rel="prefetch" href="/blog/assets/js/7.63c57787.js"><link rel="prefetch" href="/blog/assets/js/70.a2307508.js"><link rel="prefetch" href="/blog/assets/js/71.75bc515a.js"><link rel="prefetch" href="/blog/assets/js/72.f7d10a87.js"><link rel="prefetch" href="/blog/assets/js/73.ae777bb5.js"><link rel="prefetch" href="/blog/assets/js/74.ddd90b9a.js"><link rel="prefetch" href="/blog/assets/js/75.5d297adc.js"><link rel="prefetch" href="/blog/assets/js/76.299aaa21.js"><link rel="prefetch" href="/blog/assets/js/77.c93068f7.js"><link rel="prefetch" href="/blog/assets/js/78.727916c9.js"><link rel="prefetch" href="/blog/assets/js/79.605f2aff.js"><link rel="prefetch" href="/blog/assets/js/8.22284503.js"><link rel="prefetch" href="/blog/assets/js/80.0081929d.js"><link rel="prefetch" href="/blog/assets/js/9.8e870e00.js">
    <link rel="stylesheet" href="/blog/assets/css/0.styles.01ca31a9.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><div class="theme-container" data-v-19557b78><div data-v-19557b78><div id="loader-wrapper" class="loading-wrapper" data-v-d48f4d20 data-v-19557b78 data-v-19557b78><div class="loader-main" data-v-d48f4d20><div data-v-d48f4d20></div><div data-v-d48f4d20></div><div data-v-d48f4d20></div><div data-v-d48f4d20></div></div> <!----> <!----></div> <div class="password-shadow password-wrapper-out" style="display:none;" data-v-64685f0e data-v-19557b78 data-v-19557b78><h3 class="title" style="display:none;" data-v-64685f0e data-v-64685f0e>寒冷如铁</h3> <!----> <label id="box" class="inputBox" style="display:none;" data-v-64685f0e data-v-64685f0e><input type="password" value="" data-v-64685f0e> <span data-v-64685f0e>Konck! Knock!</span> <button data-v-64685f0e>OK</button></label> <div class="footer" style="display:none;" data-v-64685f0e data-v-64685f0e><span data-v-64685f0e><i class="iconfont reco-theme" data-v-64685f0e></i> <a target="blank" href="https://vuepress-theme-reco.recoluan.com" data-v-64685f0e>vuePress-theme-reco</a></span> <span data-v-64685f0e><i class="iconfont reco-copyright" data-v-64685f0e></i> <a data-v-64685f0e><span data-v-64685f0e>寒铁</span>
            
          <span data-v-64685f0e>2020 - </span>
          2022
        </a></span></div></div> <div class="hide" data-v-19557b78><header class="navbar" data-v-19557b78><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/blog/" class="home-link router-link-active"><!----> <span class="site-name">寒冷如铁</span></a> <div class="links"><div class="color-picker"><a class="color-button"><i class="iconfont reco-color"></i></a> <div class="color-picker-menu" style="display:none;"><div class="mode-options"><h4 class="title">Choose mode</h4> <ul class="color-mode-options"><li class="dark">dark</li><li class="auto active">auto</li><li class="light">light</li></ul></div></div></div> <div class="search-box"><i class="iconfont reco-search"></i> <input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/blog/" class="nav-link"><i class="iconfont reco-home"></i>
  首页
</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-category"></i>
      分类
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/blog/categories/build/" class="nav-link"><i class="iconfont undefined"></i>
  build
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/db/" class="nav-link"><i class="iconfont undefined"></i>
  db
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/docker/" class="nav-link"><i class="iconfont undefined"></i>
  docker
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/workflow/" class="nav-link"><i class="iconfont undefined"></i>
  workflow
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/git/" class="nav-link"><i class="iconfont undefined"></i>
  git
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/java/" class="nav-link"><i class="iconfont undefined"></i>
  java
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/source/" class="nav-link"><i class="iconfont undefined"></i>
  source
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/linux/" class="nav-link"><i class="iconfont undefined"></i>
  linux
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/network/" class="nav-link"><i class="iconfont undefined"></i>
  network
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/python/" class="nav-link"><i class="iconfont undefined"></i>
  python
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/project/" class="nav-link"><i class="iconfont undefined"></i>
  project
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/share/" class="nav-link"><i class="iconfont undefined"></i>
  share
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/software/" class="nav-link"><i class="iconfont undefined"></i>
  software
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/spring/" class="nav-link"><i class="iconfont undefined"></i>
  spring
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/tomcat/" class="nav-link"><i class="iconfont undefined"></i>
  tomcat
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/web/" class="nav-link"><i class="iconfont undefined"></i>
  web
</a></li></ul></div></div><div class="nav-item"><a href="/blog/tag/" class="nav-link"><i class="iconfont reco-tag"></i>
  标签
</a></div><div class="nav-item"><a href="/blog/views/guide.html" class="nav-link"><i class="iconfont reco-other"></i>
  记录
</a></div><div class="nav-item"><a href="/blog/timeline/" class="nav-link"><i class="iconfont reco-date"></i>
  时间线
</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-message"></i>
      外链
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="https://github.com/huhuhan" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-github"></i>
  GitHub
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-item"><!----> <a href="https://gitee.com/huhu_han" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-other"></i>
  Gitee
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-item"><!----> <a href="https://console.leancloud.app/#/apps" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-other"></i>
  有道云笔记
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-item"><!----> <a href="https://console.leancloud.app/#/apps" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-other"></i>
  LeadCloud
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-item"><!----> <a href="https://cloud.seafile.com/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-other"></i>
  Seafile
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-item"><!----> <a href="https://huhu_han.gitee.io/downgit" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-other"></i>
  DownGit
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul></div></div> <!----></nav></div></header> <div class="sidebar-mask" data-v-19557b78></div> <aside class="sidebar" data-v-19557b78><div class="personal-info-wrapper" data-v-b038cec6><img src="/blog/avatar.png" alt="author-avatar" class="personal-img" data-v-b038cec6> <h3 class="name" data-v-b038cec6>
    寒铁
  </h3> <div class="num" data-v-b038cec6><div data-v-b038cec6><h3 data-v-b038cec6>66</h3> <h6 data-v-b038cec6>文章</h6></div> <div data-v-b038cec6><h3 data-v-b038cec6>17</h3> <h6 data-v-b038cec6>标签</h6></div></div> <hr data-v-b038cec6></div> <nav class="nav-links"><div class="nav-item"><a href="/blog/" class="nav-link"><i class="iconfont reco-home"></i>
  首页
</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-category"></i>
      分类
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/blog/categories/build/" class="nav-link"><i class="iconfont undefined"></i>
  build
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/db/" class="nav-link"><i class="iconfont undefined"></i>
  db
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/docker/" class="nav-link"><i class="iconfont undefined"></i>
  docker
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/workflow/" class="nav-link"><i class="iconfont undefined"></i>
  workflow
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/git/" class="nav-link"><i class="iconfont undefined"></i>
  git
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/java/" class="nav-link"><i class="iconfont undefined"></i>
  java
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/source/" class="nav-link"><i class="iconfont undefined"></i>
  source
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/linux/" class="nav-link"><i class="iconfont undefined"></i>
  linux
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/network/" class="nav-link"><i class="iconfont undefined"></i>
  network
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/python/" class="nav-link"><i class="iconfont undefined"></i>
  python
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/project/" class="nav-link"><i class="iconfont undefined"></i>
  project
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/share/" class="nav-link"><i class="iconfont undefined"></i>
  share
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/software/" class="nav-link"><i class="iconfont undefined"></i>
  software
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/spring/" class="nav-link"><i class="iconfont undefined"></i>
  spring
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/tomcat/" class="nav-link"><i class="iconfont undefined"></i>
  tomcat
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/web/" class="nav-link"><i class="iconfont undefined"></i>
  web
</a></li></ul></div></div><div class="nav-item"><a href="/blog/tag/" class="nav-link"><i class="iconfont reco-tag"></i>
  标签
</a></div><div class="nav-item"><a href="/blog/views/guide.html" class="nav-link"><i class="iconfont reco-other"></i>
  记录
</a></div><div class="nav-item"><a href="/blog/timeline/" class="nav-link"><i class="iconfont reco-date"></i>
  时间线
</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-message"></i>
      外链
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="https://github.com/huhuhan" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-github"></i>
  GitHub
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-item"><!----> <a href="https://gitee.com/huhu_han" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-other"></i>
  Gitee
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-item"><!----> <a href="https://console.leancloud.app/#/apps" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-other"></i>
  有道云笔记
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-item"><!----> <a href="https://console.leancloud.app/#/apps" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-other"></i>
  LeadCloud
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-item"><!----> <a href="https://cloud.seafile.com/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-other"></i>
  Seafile
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-item"><!----> <a href="https://huhu_han.gitee.io/downgit" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-other"></i>
  DownGit
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul></div></div> <!----></nav>  <ul class="sidebar-links"><li><section class="sidebar-group depth-0"><p class="sidebar-heading open"><span>MySQL 相关总结</span> <!----></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/blog/views/db/mysql.html#疑难随笔" class="sidebar-link">疑难随笔</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/blog/views/db/mysql.html#too-many-connections" class="sidebar-link">Too many connections</a></li><li class="sidebar-sub-header"><a href="/blog/views/db/mysql.html#is-not-allowed-to-connect" class="sidebar-link">Is not allowed to connect</a></li><li class="sidebar-sub-header"><a href="/blog/views/db/mysql.html#时间差8小时" class="sidebar-link">时间差8小时</a></li></ul></li><li><a href="/blog/views/db/mysql.html#sql" class="sidebar-link">SQL</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/blog/views/db/mysql.html#优化" class="sidebar-link">优化</a></li><li class="sidebar-sub-header"><a href="/blog/views/db/mysql.html#查看系统信息" class="sidebar-link">查看系统信息</a></li><li class="sidebar-sub-header"><a href="/blog/views/db/mysql.html#批量替换字段里的字符串" class="sidebar-link">批量替换字段里的字符串</a></li><li class="sidebar-sub-header"><a href="/blog/views/db/mysql.html#函数-字符串" class="sidebar-link">函数：字符串</a></li><li class="sidebar-sub-header"><a href="/blog/views/db/mysql.html#查询阻塞情况" class="sidebar-link">查询阻塞情况</a></li><li class="sidebar-sub-header"><a href="/blog/views/db/mysql.html#建表语句" class="sidebar-link">建表语句</a></li></ul></li><li><a href="/blog/views/db/mysql.html#安装" class="sidebar-link">安装</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/blog/views/db/mysql.html#压缩包" class="sidebar-link">压缩包</a></li><li class="sidebar-sub-header"><a href="/blog/views/db/mysql.html#docker" class="sidebar-link">Docker</a></li></ul></li><li><a href="/blog/views/db/mysql.html#备份还原" class="sidebar-link">备份还原</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/blog/views/db/mysql.html#备份操作" class="sidebar-link">备份操作</a></li><li class="sidebar-sub-header"><a href="/blog/views/db/mysql.html#还原操作" class="sidebar-link">还原操作</a></li></ul></li></ul></section></li></ul> </aside> <div class="password-shadow password-wrapper-in" style="display:none;" data-v-64685f0e data-v-19557b78><h3 class="title" style="display:none;" data-v-64685f0e data-v-64685f0e>MySQL 相关总结</h3> <!----> <label id="box" class="inputBox" style="display:none;" data-v-64685f0e data-v-64685f0e><input type="password" value="" data-v-64685f0e> <span data-v-64685f0e>Konck! Knock!</span> <button data-v-64685f0e>OK</button></label> <div class="footer" style="display:none;" data-v-64685f0e data-v-64685f0e><span data-v-64685f0e><i class="iconfont reco-theme" data-v-64685f0e></i> <a target="blank" href="https://vuepress-theme-reco.recoluan.com" data-v-64685f0e>vuePress-theme-reco</a></span> <span data-v-64685f0e><i class="iconfont reco-copyright" data-v-64685f0e></i> <a data-v-64685f0e><span data-v-64685f0e>寒铁</span>
            
          <span data-v-64685f0e>2020 - </span>
          2022
        </a></span></div></div> <div data-v-19557b78><main class="page"><div class="page-title" style="display:none;"><h1>MySQL 相关总结</h1> <hr> <div data-v-484a899e><i class="iconfont reco-account" data-v-484a899e><span data-v-484a899e>寒铁</span></i> <i class="iconfont reco-date" data-v-484a899e><span data-v-484a899e>2020-03-04</span></i> <i class="iconfont reco-eye" data-v-484a899e><span id="/blog/views/db/mysql.html" data-flag-title="Your Article Title" class="leancloud-visitors" data-v-484a899e><a class="leancloud-visitors-count" style="font-size:.9rem;font-weight:normal;color:#999;"></a></span></i> <i class="iconfont reco-tag tags" data-v-484a899e><span class="tag-item" data-v-484a899e>
      db
    </span></i></div></div> <div class="theme-reco-content content__default" style="display:none;"><h2 id="疑难随笔"><a href="#疑难随笔" class="header-anchor">#</a> 疑难随笔</h2> <h3 id="too-many-connections"><a href="#too-many-connections" class="header-anchor">#</a> Too many connections</h3> <p>原因：</p> <ul><li><p>数据库连接都在Sleep，默认8小时，一般是没有正常关闭造成</p></li> <li><p>数据库最大连接数太少，默认20，仅限于并大连接情况下</p></li></ul> <p>解决：</p> <ul><li><p>wait_timeout：<strong>非交互连接</strong>前等待秒数。比如<strong>JDBC连接的通信</strong></p></li> <li><p>interactive_timeout：<strong>交互式连接</strong>前等待秒数，比如<strong>客户端连接</strong></p></li> <li><p>修改配置文件<code>my.ini</code>参数<code>max_connections</code>，需重启</p> <div class="language-ini line-numbers-mode"><pre class="language-ini"><code><span class="token comment"># 允许最大连接数，默认20</span>
<span class="token constant">max_connections</span><span class="token attr-value"><span class="token punctuation">=</span>20</span>
<span class="token comment"># 超时时间，单位秒，默认28800</span>
<span class="token constant">interactive_timeout</span> <span class="token attr-value"><span class="token punctuation">=</span> 3600</span>
<span class="token constant">wait_timeout</span> <span class="token attr-value"><span class="token punctuation">=</span> 3600</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div></li></ul> <h3 id="is-not-allowed-to-connect"><a href="#is-not-allowed-to-connect" class="header-anchor">#</a> Is not allowed to connect</h3> <p>原因是root账户被限制只限制本地localhost连接，先本地连接再修改为<code>%</code>权限即可，如下</p> <p><img src="https://fastly.jsdelivr.net/gh/huhuhan/img@master/md/pic/20200731134830.png" alt=""></p> <p>命令修改方式</p> <div class="language-shell line-numbers-mode"><pre class="language-shell"><code><span class="token comment"># 登录root，输入密码回车（第一次，免密登录，直接回车）</span>
mysql -u root -p
<span class="token comment"># 切换到mysql</span>
use mysql
<span class="token comment"># 直接查询</span>
<span class="token keyword">select</span> <span class="token function">host</span> from mysql.user where <span class="token assign-left variable">user</span><span class="token operator">=</span><span class="token string">'root'</span><span class="token punctuation">;</span>
<span class="token comment"># 修改host</span>
update mysql.user <span class="token builtin class-name">set</span> <span class="token assign-left variable">host</span><span class="token operator">=</span><span class="token string">'%'</span> where <span class="token assign-left variable">user</span><span class="token operator">=</span><span class="token string">'root'</span>
<span class="token comment"># 修改密码</span>
update mysql.user <span class="token builtin class-name">set</span> <span class="token assign-left variable">authentication_string</span><span class="token operator">=</span><span class="token string">'111111yh!'</span> where <span class="token assign-left variable">user</span><span class="token operator">=</span><span class="token string">'root'</span>
<span class="token comment"># 刷新</span>
flush privileges<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br></div></div><h3 id="时间差8小时"><a href="#时间差8小时" class="header-anchor">#</a> 时间差8小时</h3> <ul><li><p>Java注解</p> <div class="language-java line-numbers-mode"><pre class="language-java"><code><span class="token keyword">import</span> <span class="token namespace">com<span class="token punctuation">.</span>fasterxml<span class="token punctuation">.</span>jackson<span class="token punctuation">.</span>annotation<span class="token punctuation">.</span></span><span class="token class-name">JsonFormat</span><span class="token punctuation">;</span>
<span class="token annotation punctuation">@JsonFormat</span><span class="token punctuation">(</span>shape <span class="token operator">=</span> <span class="token class-name">JsonFormat<span class="token punctuation">.</span>Shape</span><span class="token punctuation">.</span>STRING<span class="token punctuation">,</span> pattern <span class="token operator">=</span> <span class="token string">&quot;yyyy-MM-dd HH:mm:ss&quot;</span><span class="token punctuation">,</span> timezone <span class="token operator">=</span> <span class="token string">&quot;GMT+08&quot;</span><span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div></li></ul> <h2 id="sql"><a href="#sql" class="header-anchor">#</a> SQL</h2> <h3 id="优化"><a href="#优化" class="header-anchor">#</a> 优化</h3> <p><img src="https://fastly.jsdelivr.net/gh/huhuhan/img@master/md/pic/202205111728865.jpg" alt=""></p> <h3 id="查看系统信息"><a href="#查看系统信息" class="header-anchor">#</a> 查看系统信息</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 以下通过视图查询</span>
<span class="token comment">-- 查询相关超时配置，单位秒</span>
<span class="token keyword">show</span> <span class="token keyword">global</span> variables <span class="token operator">like</span> <span class="token string">'%timeout%'</span>
<span class="token comment">-- 查询有效最大连接数</span>
<span class="token keyword">show</span> variables <span class="token operator">like</span> <span class="token string">'max_connections'</span>
<span class="token comment">-- 查询已用最大连接数据，最大=max_connections+1，因为会留一条给root用户连接，mysql的机制</span>
<span class="token keyword">show</span> <span class="token keyword">status</span> <span class="token operator">like</span> <span class="token string">'max_used_connections'</span>
<span class="token comment">-- 查询所有连接，可看到连接信息</span>
<span class="token keyword">show</span> <span class="token keyword">full</span> processlist<span class="token punctuation">;</span>

<span class="token comment">-- 应用可优化interactive_timeout超时，不可小，默认8小时</span>
<span class="token keyword">set</span> <span class="token keyword">global</span> interactive_timeout<span class="token operator">=</span><span class="token number">300</span><span class="token punctuation">;</span>
<span class="token comment">-- 扩大最大连接数</span>
<span class="token keyword">set</span> <span class="token keyword">GLOBAL</span> max_connections<span class="token operator">=</span><span class="token number">30</span>

<span class="token comment">-- 查询大小写配置属性</span>
<span class="token keyword">show</span> variables <span class="token operator">like</span> <span class="token string">'lower_case_table_names'</span>

<span class="token comment">-- 最大数据包，默认4M，即一次sql查询的数据量，一般表数据库量增大了，需要修改</span>
<span class="token keyword">show</span> VARIABLES <span class="token operator">like</span> <span class="token string">'%max_allowed_packet%'</span><span class="token punctuation">;</span>

<span class="token comment">-- binlog配置</span>
<span class="token keyword">show</span> variables <span class="token operator">like</span> <span class="token string">'%log_bin%'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br></div></div><h3 id="批量替换字段里的字符串"><a href="#批量替换字段里的字符串" class="header-anchor">#</a> 批量替换字段里的字符串</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">update</span> Table_Name <span class="token keyword">set</span> Column_Name<span class="token operator">=</span><span class="token keyword">replace</span><span class="token punctuation">(</span>Column_Name<span class="token punctuation">,</span><span class="token string">'Content'</span><span class="token punctuation">,</span><span class="token string">'Replace_Content'</span><span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h3 id="函数-字符串"><a href="#函数-字符串" class="header-anchor">#</a> 函数：字符串</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span>
	a <span class="token keyword">AS</span> <span class="token string">'原字段'</span><span class="token punctuation">,</span>
	substring<span class="token punctuation">(</span> t<span class="token punctuation">.</span>a<span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">5</span> <span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'第1位开始截取，长度5'</span><span class="token punctuation">,</span>
	reverse<span class="token punctuation">(</span> t<span class="token punctuation">.</span>a <span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'反转字符串'</span><span class="token punctuation">,</span>
	locate<span class="token punctuation">(</span> <span class="token string">'.'</span><span class="token punctuation">,</span> t<span class="token punctuation">.</span>a <span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'字符下标，1开始'</span><span class="token punctuation">,</span>
	length<span class="token punctuation">(</span> t<span class="token punctuation">.</span>a <span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token string">'长度'</span> 
<span class="token keyword">FROM</span>
	<span class="token punctuation">(</span> <span class="token keyword">SELECT</span> <span class="token string">'1.234.5'</span> <span class="token keyword">AS</span> a <span class="token punctuation">)</span> <span class="token keyword">AS</span> t
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><h3 id="查询阻塞情况"><a href="#查询阻塞情况" class="header-anchor">#</a> 查询阻塞情况</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span>
	p2<span class="token punctuation">.</span><span class="token punctuation">`</span>HOST<span class="token punctuation">`</span> <span class="token string">'被阻塞方HOST'</span><span class="token punctuation">,</span>
	p2<span class="token punctuation">.</span><span class="token punctuation">`</span><span class="token keyword">USER</span><span class="token punctuation">`</span> <span class="token string">'被阻塞方用户'</span><span class="token punctuation">,</span>
	r<span class="token punctuation">.</span>trx_id <span class="token string">'被阻塞方事务id'</span><span class="token punctuation">,</span>
	r<span class="token punctuation">.</span>trx_mysql_thread_id <span class="token string">'被阻塞方线程号'</span><span class="token punctuation">,</span>
	TIMESTAMPDIFF<span class="token punctuation">(</span> <span class="token keyword">SECOND</span><span class="token punctuation">,</span> r<span class="token punctuation">.</span>trx_wait_started<span class="token punctuation">,</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token punctuation">)</span> <span class="token string">'等待时间'</span><span class="token punctuation">,</span>
	r<span class="token punctuation">.</span>trx_query <span class="token string">'被阻塞的查询'</span><span class="token punctuation">,</span>
	l<span class="token punctuation">.</span>lock_table <span class="token string">'阻塞方锁住的表'</span><span class="token punctuation">,</span>
	m<span class="token punctuation">.</span><span class="token punctuation">`</span>lock_mode<span class="token punctuation">`</span> <span class="token string">'被阻塞方的锁模式'</span><span class="token punctuation">,</span>
	m<span class="token punctuation">.</span><span class="token punctuation">`</span>lock_type<span class="token punctuation">`</span> <span class="token string">&quot;被阻塞方的锁类型(表锁还是行锁)&quot;</span><span class="token punctuation">,</span>
	m<span class="token punctuation">.</span><span class="token punctuation">`</span>lock_index<span class="token punctuation">`</span> <span class="token string">'被阻塞方锁住的索引'</span><span class="token punctuation">,</span>
	m<span class="token punctuation">.</span><span class="token punctuation">`</span>lock_space<span class="token punctuation">`</span> <span class="token string">'被阻塞方锁对象的space_id'</span><span class="token punctuation">,</span>
	m<span class="token punctuation">.</span>lock_page <span class="token string">'被阻塞方事务锁定页的数量'</span><span class="token punctuation">,</span>
	m<span class="token punctuation">.</span>lock_rec <span class="token string">'被阻塞方事务锁定行的数量'</span><span class="token punctuation">,</span>
	m<span class="token punctuation">.</span>lock_data <span class="token string">'被阻塞方事务锁定记录的主键值'</span><span class="token punctuation">,</span>
	p<span class="token punctuation">.</span><span class="token punctuation">`</span>HOST<span class="token punctuation">`</span> <span class="token string">'阻塞方主机'</span><span class="token punctuation">,</span>
	p<span class="token punctuation">.</span><span class="token punctuation">`</span><span class="token keyword">USER</span><span class="token punctuation">`</span> <span class="token string">'阻塞方用户'</span><span class="token punctuation">,</span>
	b<span class="token punctuation">.</span>trx_id <span class="token string">'阻塞方事务id'</span><span class="token punctuation">,</span>
	b<span class="token punctuation">.</span>trx_mysql_thread_id <span class="token string">'阻塞方线程号'</span><span class="token punctuation">,</span>
	b<span class="token punctuation">.</span>trx_query <span class="token string">'阻塞方查询'</span><span class="token punctuation">,</span>
	l<span class="token punctuation">.</span><span class="token punctuation">`</span>lock_mode<span class="token punctuation">`</span> <span class="token string">'阻塞方的锁模式'</span><span class="token punctuation">,</span>
	l<span class="token punctuation">.</span><span class="token punctuation">`</span>lock_type<span class="token punctuation">`</span> <span class="token string">'阻塞方的锁类型(表锁还是行锁)'</span><span class="token punctuation">,</span>
	l<span class="token punctuation">.</span><span class="token punctuation">`</span>lock_index<span class="token punctuation">`</span> <span class="token string">'阻塞方锁住的索引'</span><span class="token punctuation">,</span>
	l<span class="token punctuation">.</span><span class="token punctuation">`</span>lock_space<span class="token punctuation">`</span> <span class="token string">'阻塞方锁对象的space_id'</span><span class="token punctuation">,</span>
	l<span class="token punctuation">.</span>lock_page <span class="token string">'阻塞方事务锁定页的数量'</span><span class="token punctuation">,</span>
	l<span class="token punctuation">.</span>lock_rec <span class="token string">'阻塞方事务锁定行的数量'</span><span class="token punctuation">,</span>
	l<span class="token punctuation">.</span>lock_data <span class="token string">'阻塞方事务锁定记录的主键值'</span><span class="token punctuation">,</span>
<span class="token keyword">IF</span>
	<span class="token punctuation">(</span> p<span class="token punctuation">.</span>COMMAND <span class="token operator">=</span> <span class="token string">'Sleep'</span><span class="token punctuation">,</span> CONCAT<span class="token punctuation">(</span> p<span class="token punctuation">.</span><span class="token keyword">TIME</span><span class="token punctuation">,</span> <span class="token string">' 秒'</span> <span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token number">0</span> <span class="token punctuation">)</span> <span class="token string">'阻塞方事务空闲的时间'</span> 
<span class="token keyword">FROM</span>
	information_schema<span class="token punctuation">.</span>INNODB_LOCK_WAITS w
	<span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> information_schema<span class="token punctuation">.</span>INNODB_TRX b <span class="token keyword">ON</span> b<span class="token punctuation">.</span>trx_id <span class="token operator">=</span> w<span class="token punctuation">.</span>blocking_trx_id
	<span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> information_schema<span class="token punctuation">.</span>INNODB_TRX r <span class="token keyword">ON</span> r<span class="token punctuation">.</span>trx_id <span class="token operator">=</span> w<span class="token punctuation">.</span>requesting_trx_id
	<span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> information_schema<span class="token punctuation">.</span>INNODB_LOCKS l <span class="token keyword">ON</span> w<span class="token punctuation">.</span>blocking_lock_id <span class="token operator">=</span> l<span class="token punctuation">.</span>lock_id 
	<span class="token operator">AND</span> l<span class="token punctuation">.</span><span class="token punctuation">`</span>lock_trx_id<span class="token punctuation">`</span> <span class="token operator">=</span> b<span class="token punctuation">.</span><span class="token punctuation">`</span>trx_id<span class="token punctuation">`</span>
	<span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> information_schema<span class="token punctuation">.</span>INNODB_LOCKS m <span class="token keyword">ON</span> m<span class="token punctuation">.</span><span class="token punctuation">`</span>lock_id<span class="token punctuation">`</span> <span class="token operator">=</span> w<span class="token punctuation">.</span><span class="token punctuation">`</span>requested_lock_id<span class="token punctuation">`</span> 
	<span class="token operator">AND</span> m<span class="token punctuation">.</span><span class="token punctuation">`</span>lock_trx_id<span class="token punctuation">`</span> <span class="token operator">=</span> r<span class="token punctuation">.</span><span class="token punctuation">`</span>trx_id<span class="token punctuation">`</span>
	<span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> information_schema<span class="token punctuation">.</span>PROCESSLIST p <span class="token keyword">ON</span> p<span class="token punctuation">.</span>ID <span class="token operator">=</span> b<span class="token punctuation">.</span>trx_mysql_thread_id
	<span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> information_schema<span class="token punctuation">.</span>PROCESSLIST p2 <span class="token keyword">ON</span> p2<span class="token punctuation">.</span>ID <span class="token operator">=</span> r<span class="token punctuation">.</span>trx_mysql_thread_id 
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span>
	<span class="token string">'等待时间'</span> <span class="token keyword">DESC</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br><span class="line-number">40</span><br><span class="line-number">41</span><br></div></div><h3 id="建表语句"><a href="#建表语句" class="header-anchor">#</a> 建表语句</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 建表语句</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span>demo_user<span class="token punctuation">`</span> <span class="token punctuation">(</span>
  <span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">bigint</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">COMMENT</span> <span class="token string">'主键'</span><span class="token punctuation">,</span>
  <span class="token punctuation">`</span>user_name_<span class="token punctuation">`</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">64</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span> <span class="token keyword">COMMENT</span> <span class="token string">'用户名'</span><span class="token punctuation">,</span>
  <span class="token punctuation">`</span>age_<span class="token punctuation">`</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span> <span class="token keyword">COMMENT</span> <span class="token string">'年龄'</span><span class="token punctuation">,</span>
  <span class="token punctuation">`</span>money_<span class="token punctuation">`</span> <span class="token keyword">double</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span> <span class="token keyword">COMMENT</span> <span class="token string">'浮点数例子'</span><span class="token punctuation">,</span>
  <span class="token punctuation">`</span>sex_<span class="token punctuation">`</span> <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token string">'00'</span> <span class="token keyword">COMMENT</span> <span class="token string">'性别：男01，女02，未知00'</span><span class="token punctuation">,</span>
  <span class="token punctuation">`</span>delete_flag_<span class="token punctuation">`</span> <span class="token keyword">tinyint</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token string">'0'</span> <span class="token keyword">COMMENT</span> <span class="token string">'是否删除，0否1是'</span><span class="token punctuation">,</span>
  <span class="token punctuation">`</span>create_time_<span class="token punctuation">`</span> <span class="token keyword">datetime</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">COMMENT</span> <span class="token string">'创建时间'</span><span class="token punctuation">,</span>
  <span class="token punctuation">`</span>update_time_<span class="token punctuation">`</span> <span class="token keyword">timestamp</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">COMMENT</span> <span class="token string">'更新时间'</span><span class="token punctuation">,</span>
  <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">InnoDB</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8 <span class="token keyword">COMMENT</span><span class="token operator">=</span><span class="token string">'例子参考-建表语句'</span><span class="token punctuation">;</span>

<span class="token comment">-- 新增字段</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span>demo_user<span class="token punctuation">`</span> <span class="token keyword">ADD</span> <span class="token keyword">COLUMN</span> <span class="token punctuation">`</span>dd<span class="token punctuation">`</span> <span class="token keyword">VARCHAR</span> <span class="token punctuation">(</span> <span class="token number">255</span> <span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span> <span class="token keyword">COMMENT</span> <span class="token string">'dddd'</span><span class="token punctuation">;</span>
<span class="token comment">-- 删除字段</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span>demo_user<span class="token punctuation">`</span> <span class="token keyword">DROP</span> <span class="token keyword">COLUMN</span> <span class="token punctuation">`</span>dd<span class="token punctuation">`</span><span class="token punctuation">;</span>

<span class="token comment">-- 修改字段</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span>demo_user<span class="token punctuation">`</span> <span class="token keyword">MODIFY</span> <span class="token keyword">COLUMN</span> <span class="token punctuation">`</span>money_<span class="token punctuation">`</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span> <span class="token keyword">COMMENT</span> <span class="token string">'浮点数例子'</span> <span class="token keyword">AFTER</span> <span class="token punctuation">`</span>age_<span class="token punctuation">`</span><span class="token punctuation">;</span>

<span class="token comment">-- 新增索引</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span>demo_user<span class="token punctuation">`</span> <span class="token keyword">ADD</span> <span class="token keyword">INDEX</span> <span class="token punctuation">`</span>index_age_<span class="token punctuation">`</span><span class="token punctuation">(</span><span class="token punctuation">`</span>age_<span class="token punctuation">`</span><span class="token punctuation">)</span> <span class="token keyword">USING</span> <span class="token keyword">BTREE</span><span class="token punctuation">;</span>
<span class="token comment">-- 删除索引</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span>demo_user<span class="token punctuation">`</span> <span class="token keyword">DROP</span> <span class="token keyword">INDEX</span> <span class="token punctuation">`</span>index_age_<span class="token punctuation">`</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br></div></div><h2 id="安装"><a href="#安装" class="header-anchor">#</a> 安装</h2> <blockquote><p>忽略大小配置<strong>lower_case_table_names</strong>，取决于底层文件系统</p> <ul><li>Linux，默认0，区分大小写</li> <li>windows，默认为1，忽略大小写，以小写查询、保存表名</li> <li>MacOS，默认为2，根据文件系统选择决定</li></ul></blockquote> <h3 id="压缩包"><a href="#压缩包" class="header-anchor">#</a> 压缩包</h3> <ol><li><p><code>my.ini</code>文件复制至解压目录下</p> <div class="language-ini line-numbers-mode"><pre class="language-ini"><code><span class="token selector">[mysql]</span>
<span class="token comment"># 设置mysql客户端默认字符集</span>
<span class="token constant">default-character-set</span><span class="token attr-value"><span class="token punctuation">=</span>utf8</span>
<span class="token selector">[mysqld]</span>
<span class="token comment"># 设置3306端口</span>
<span class="token constant">port</span> <span class="token attr-value"><span class="token punctuation">=</span> 3306</span>
<span class="token comment"># 设置mysql的安装目录</span>
<span class="token constant">basedir</span><span class="token attr-value"><span class="token punctuation">=</span>D:\\Program Files\\Mysql\\mysql-5.7.26-winx64</span>
<span class="token comment"># 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错</span>
<span class="token constant">datadir</span><span class="token attr-value"><span class="token punctuation">=</span>D:\\Program Files\\Mysql\\mysql-5.7.26-winx64\\sqldata</span>
<span class="token comment"># 允许最大连接数</span>
<span class="token constant">max_connections</span><span class="token attr-value"><span class="token punctuation">=</span>20</span>
<span class="token comment"># 服务端使用的字符集默认为8比特编码的latin1字符集</span>
<span class="token constant">character-set-server</span><span class="token attr-value"><span class="token punctuation">=</span>utf8</span>
<span class="token comment"># 创建新表时将使用的默认存储引擎</span>
<span class="token constant">default-storage-engine</span><span class="token attr-value"><span class="token punctuation">=</span>INNODB</span>
<span class="token constant">explicit_defaults_for_timestamp</span><span class="token attr-value"><span class="token punctuation">=</span>true</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br></div></div></li> <li><p>配置bin环境变量</p></li> <li><p>进去bin目录下运行命令</p> <div class="language-shell line-numbers-mode"><pre class="language-shell"><code><span class="token comment"># 安装</span>
mysqld -install
<span class="token comment"># 若已存在，则执行删除，再安装</span>
mysqld -remove

<span class="token comment"># 初始化mysql命令，data目录并生成root用户</span>
mysqld --initialize-insecure --user<span class="token operator">=</span>mysql

<span class="token comment"># 启动</span>
net start mysql

<span class="token comment"># 登录，修改密码，默认为空</span>
mysqladmin -u root -p password
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br></div></div></li></ol> <h3 id="docker"><a href="#docker" class="header-anchor">#</a> Docker</h3> <h4 id="默认结构"><a href="#默认结构" class="header-anchor">#</a> 默认结构</h4> <blockquote><p><strong>可跳过不看</strong>。先了解原始容器初始化的文件结构，才能根据需求修改初始化配置参数</p></blockquote> <div class="language-yml line-numbers-mode"><pre class="language-yml"><code><span class="token key atrule">version</span><span class="token punctuation">:</span> <span class="token string">'3'</span>
<span class="token key atrule">services</span><span class="token punctuation">:</span>
  <span class="token key atrule">mysql</span><span class="token punctuation">:</span>
    <span class="token key atrule">environment</span><span class="token punctuation">:</span>
      <span class="token key atrule">MYSQL_ROOT_PASSWORD</span><span class="token punctuation">:</span> <span class="token string">&quot;111111yh!&quot;</span>
    <span class="token key atrule">image</span><span class="token punctuation">:</span> <span class="token string">&quot;mysql:5.7.26&quot;</span>
    <span class="token key atrule">container_name</span><span class="token punctuation">:</span> mysql
    <span class="token key atrule">ports</span><span class="token punctuation">:</span>
      <span class="token punctuation">-</span> <span class="token string">&quot;8306:3306&quot;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><div class="language-shell line-numbers-mode"><pre class="language-shell"><code><span class="token comment"># 进入容器</span>
docker <span class="token builtin class-name">exec</span> -it <span class="token punctuation">[</span>容器ID<span class="token punctuation">]</span> /bin/bash
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>进入容器的配置目录<code>/etc/mysql</code>可以发现如下</p> <p><img src="https://fastly.jsdelivr.net/gh/huhuhan/img@master/md/pic/20201216140414.png" alt=""></p> <p>其中<strong><em>cnf</em>后缀</strong>是配置文件，<strong><em>conf.d</em>后缀</strong>的是配置目录，其中<strong>my.cnf</strong>和<strong>mysql.cnf</strong>的默认内容</p> <div class="language-ini line-numbers-mode"><pre class="language-ini"><code><span class="token comment"># 表示引入这个两目录中的配置</span>
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>因此，自定义配置文件</p> <ul><li>可挂载在<code>conf.d、mysql.conf.d</code>这两目录下面</li> <li>或者直接替换<code>my.cnf、mysql.cnf</code>其中一个文件。</li></ul> <p>其中<code>conf.d/mysql.cnf</code>，默认配置如下</p> <div class="language-ini line-numbers-mode"><pre class="language-ini"><code><span class="token selector">[mysql]</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>其中<code>mysql.conf.d/mysqld.cnf</code>，默认配置如下，<strong>推荐挂载这文件，注意是[mysqld]，服务端配置</strong></p> <div class="language-ini line-numbers-mode"><pre class="language-ini"><code><span class="token selector">[mysqld]</span>
<span class="token constant">pid-file</span>        <span class="token attr-value"><span class="token punctuation">=</span> /var/run/mysqld/mysqld.pid</span>
<span class="token constant">socket</span>          <span class="token attr-value"><span class="token punctuation">=</span> /var/run/mysqld/mysqld.sock</span>
<span class="token constant">datadir</span>         <span class="token attr-value"><span class="token punctuation">=</span> /var/lib/mysql</span>
<span class="token comment">#log-error      = /var/log/mysql/error.log</span>
<span class="token comment"># By default we only accept connections from localhost</span>
<span class="token comment">#bind-address   = 127.0.0.1</span>
<span class="token comment"># Disabling symbolic-links is recommended to prevent assorted security risks</span>
<span class="token constant">symbolic-links</span><span class="token attr-value"><span class="token punctuation">=</span>0</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><h4 id="实际配置"><a href="#实际配置" class="header-anchor">#</a> 实际配置</h4> <ul><li><p>镜像下载</p> <div class="language-shell line-numbers-mode"><pre class="language-shell"><code>docker pull mysql:5.7.26
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div></li> <li><p>选择挂载的配置是<code>/etc/mysql/mysql.conf.d/mysqld.cnf</code></p> <div class="language-ini line-numbers-mode"><pre class="language-ini"><code><span class="token comment">## 原有配置属性</span>
<span class="token comment">#</span>
<span class="token comment"># The MySQL  Server configuration file.</span>
<span class="token comment">#</span>
<span class="token comment"># For explanations see</span>
<span class="token comment"># http://dev.mysql.com/doc/mysql/en/server-system-variables.html</span>
<span class="token selector">[mysqld]</span>
<span class="token constant">pid-file</span>        <span class="token attr-value"><span class="token punctuation">=</span> /var/run/mysqld/mysqld.pid</span>
<span class="token constant">socket</span>          <span class="token attr-value"><span class="token punctuation">=</span> /var/run/mysqld/mysqld.sock</span>
<span class="token constant">datadir</span>         <span class="token attr-value"><span class="token punctuation">=</span> /var/lib/mysql</span>
<span class="token comment"># log-error      = /var/log/mysql/error.log</span>
<span class="token comment"># By default we only accept connections from localhost</span>
<span class="token comment"># bind-address   = 127.0.0.1</span>
<span class="token comment"># Disabling symbolic-links is recommended to prevent assorted security risks</span>

<span class="token comment">## 自定义属性</span>
<span class="token comment">#</span>
<span class="token comment"># 忽略大小写</span>
<span class="token constant">lower_case_table_names</span>	<span class="token attr-value"><span class="token punctuation">=</span> 1</span>
<span class="token comment"># 8.0版本的话sql_mode注释掉</span>
<span class="token constant">sql_mode</span><span class="token attr-value"><span class="token punctuation">=</span>STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION</span>
<span class="token comment"># 开启binlog日志</span>
<span class="token constant">server-id</span> <span class="token attr-value"><span class="token punctuation">=</span> 1</span>
<span class="token constant">log_bin</span> <span class="token attr-value"><span class="token punctuation">=</span> mysql-bin</span>
<span class="token constant">binlog_format</span> <span class="token attr-value"><span class="token punctuation">=</span> ROW</span>

<span class="token comment"># 设置最大缓存区，一般导入大数据时需要修改</span>
<span class="token constant">max_allowed_packet</span> <span class="token attr-value"><span class="token punctuation">=</span> 100M</span>
<span class="token comment"># 开启慢SQL日志，单位秒，指超过2秒的执行时间判断为慢SQL</span>
<span class="token constant">long_query_time</span> <span class="token attr-value"><span class="token punctuation">=</span> 2</span>
<span class="token comment"># 慢SQL日志</span>
<span class="token constant">slow_query_log</span> <span class="token attr-value"><span class="token punctuation">=</span> ON</span>
<span class="token constant">slow_query_log_file</span> <span class="token attr-value"><span class="token punctuation">=</span> /var/lib/mysql/mysql-slow.log</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br></div></div></li> <li><p>准备挂载的目录和文件，我主机根目录是<code>/sdb1/mysql</code></p> <div class="language-shell line-numbers-mode"><pre class="language-shell"><code><span class="token comment"># 1. 新建自定义配置文件，</span>
<span class="token function">mkdir</span> -p /sdb1/mysql/config
<span class="token function">touch</span> /sdb1/mysql/config/mysqld.cnf
<span class="token comment">## 复制上述配置保存（wq）</span>
<span class="token function">vim</span> /sdb1/mysql/config/mysqld.cnf
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><div class="language-shell line-numbers-mode"><pre class="language-shell"><code><span class="token comment"># 2. 可选，用于挂载数据目录（测试过会自动创建）</span>
<span class="token function">mkdir</span> -p /sdb1/mysql/data
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language-shell line-numbers-mode"><pre class="language-shell"><code><span class="token comment"># 3. 可选，用于挂载socket目录（测试会自动创建，但有权限问题）</span>
<span class="token function">mkdir</span> -p /sdb1/mysql/socket
<span class="token comment"># 修改目录所有设者权限，直接改根目录就行</span>
<span class="token function">chown</span> -R polkitd:input /sdb1/mysql
<span class="token comment"># 验证</span>
<span class="token function">ls</span> -l /sdb1/mysql
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><blockquote><p>根据需求选择挂载的数据，一般只用1、2就行。</p> <p>socket默认生成是<code>/var/run/mysqld/mysqld.sock</code>，<code>/var/run</code>是公共目录，<strong>若是不改权限直接挂载，mysql用户就没有权限生成文件，会启动失败</strong>。（不挂载内部默认是root用户）</p></blockquote></li> <li><p>根据上面选择，最终脚本文件如下</p> <div class="language-yml line-numbers-mode"><pre class="language-yml"><code><span class="token key atrule">version</span><span class="token punctuation">:</span> <span class="token string">'3'</span>
<span class="token key atrule">services</span><span class="token punctuation">:</span>
  <span class="token key atrule">mysql</span><span class="token punctuation">:</span>
    <span class="token key atrule">network_mode</span><span class="token punctuation">:</span> <span class="token string">&quot;bridge&quot;</span>
    <span class="token key atrule">environment</span><span class="token punctuation">:</span>
      <span class="token key atrule">MYSQL_ROOT_PASSWORD</span><span class="token punctuation">:</span> <span class="token string">&quot;111111yh!&quot;</span>
    <span class="token key atrule">image</span><span class="token punctuation">:</span> <span class="token string">&quot;mysql:5.7.26&quot;</span>
    <span class="token key atrule">container_name</span><span class="token punctuation">:</span> mysql
    <span class="token key atrule">restart</span><span class="token punctuation">:</span> always
    <span class="token key atrule">volumes</span><span class="token punctuation">:</span>
      <span class="token punctuation">-</span> /sdb1/mysql/data<span class="token punctuation">:</span>/var/lib/mysql
      <span class="token punctuation">-</span> /sdb1/mysql/config/mysqld.cnf<span class="token punctuation">:</span>/etc/mysql/mysql.conf.d/mysqld.cnf
      <span class="token punctuation">-</span> /sdb1/mysql/socket<span class="token punctuation">:</span>/var/run/mysqld
    <span class="token key atrule">ports</span><span class="token punctuation">:</span>
      <span class="token punctuation">-</span> <span class="token string">&quot;8306:3306&quot;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br></div></div></li> <li><p>连接数据库，验证配置</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 忽略大小写配置</span>
<span class="token keyword">show</span> variables <span class="token operator">like</span> <span class="token string">'lower_case_table_names'</span><span class="token punctuation">;</span>
<span class="token comment">-- binlog配置</span>
<span class="token keyword">show</span> variables <span class="token operator">like</span> <span class="token string">'%log_bin%'</span><span class="token punctuation">;</span>
<span class="token comment">-- ....</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div></li> <li><p>其他操作命令参考</p> <div class="language-shell line-numbers-mode"><pre class="language-shell"><code><span class="token comment"># 进入容器</span>
docker <span class="token builtin class-name">exec</span> -it mysql <span class="token function">bash</span>
<span class="token comment"># 查看日志</span>
docker logs -f mysql
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div></li></ul> <h2 id="备份还原"><a href="#备份还原" class="header-anchor">#</a> 备份还原</h2> <blockquote><p>可选用<a href="/blog/views/db/xtrabackup.html">XtraBackup</a>软件来备份</p></blockquote> <p>MySQL安装自带命令<code>mysqldump</code>，一般会直接配置好环境变量了，或去<code>/bin</code>目录下查找</p> <p>官方文档：https://dev.mysql.com/doc/refman/5.7/en/using-mysqldump.html</p> <h3 id="备份操作"><a href="#备份操作" class="header-anchor">#</a> 备份操作</h3> <div class="language-shell line-numbers-mode"><pre class="language-shell"><code><span class="token comment"># 基本参数。其中p会警告不要明文输入在命令行中，可执行后再输入</span>
<span class="token comment"># mysqldump -uroot -pyanghan -hlocalhost</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language-shell line-numbers-mode"><pre class="language-shell"><code><span class="token comment"># 备份全部数据库 至 all.sql 文件</span>
mysqldump -uroot -p -A <span class="token operator">&gt;</span> all.sql

<span class="token comment"># 备份指定数据库 test DB 至 test.sql 文件</span>
mysqldump -uroot -p <span class="token builtin class-name">test</span> <span class="token operator">&gt;</span> test.sql
<span class="token comment"># 仅备份结构</span>
mysqldump -uroot -p <span class="token builtin class-name">test</span> -d <span class="token operator">&gt;</span> test-d.sql
<span class="token comment"># 仅备份数据</span>
mysqldump -uroot -p <span class="token builtin class-name">test</span> -t <span class="token operator">&gt;</span> test-t.sql
<span class="token comment"># 备份多张表，结构参数（-d）和数据参数（-t），同上</span>
mysqldump -uroot -p <span class="token builtin class-name">test</span> org_user org_role <span class="token operator">&gt;</span> test-tables.sql

<span class="token comment"># 备份多个数据库</span>
mysqldump -uroot -p --databases test1 test2 <span class="token operator">&gt;</span> testdb.sql
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br></div></div><blockquote><p><a href="https://www.cnblogs.com/sswind/p/12072209.html" target="_blank" rel="noopener noreferrer">扩展参考<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></p></blockquote> <h3 id="还原操作"><a href="#还原操作" class="header-anchor">#</a> 还原操作</h3> <div class="language-shell line-numbers-mode"><pre class="language-shell"><code><span class="token comment"># test.sql文件即上述备份文件</span>
mysql -uroot -p <span class="token operator">&lt;</span> test.sql
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><div class="language-shell line-numbers-mode"><pre class="language-shell"><code><span class="token comment"># 登录mysql后再还原</span>
mysql -uroot -p
<span class="token comment"># 输入密码登录</span>
<span class="token comment"># source命令还原</span>
<span class="token builtin class-name">source</span> test.sql
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div></div> <footer class="page-edit" style="display:none;"><!----> <div class="last-updated"><span class="prefix">Last Updated: </span> <span class="time">2022/5/27 上午11:15:53</span></div></footer> <!----> <!----></main> <!----></div></div></div></div><div class="global-ui"><div class="back-to-ceiling" style="right:1rem;bottom:6rem;width:2.5rem;height:2.5rem;border-radius:.25rem;line-height:2.5rem;display:none;" data-v-c6073ba8 data-v-c6073ba8><svg t="1574745035067" viewBox="0 0 1024 1024" version="1.1" xmlns="http://www.w3.org/2000/svg" p-id="5404" class="icon" data-v-c6073ba8><path d="M526.60727968 10.90185116a27.675 27.675 0 0 0-29.21455937 0c-131.36607665 82.28402758-218.69155461 228.01873535-218.69155402 394.07834331a462.20625001 462.20625001 0 0 0 5.36959153 69.94390903c1.00431239 6.55289093-0.34802892 13.13561351-3.76865779 18.80351572-32.63518765 54.11355614-51.75690182 118.55860487-51.7569018 187.94566865a371.06718723 371.06718723 0 0 0 11.50484808 91.98906777c6.53300375 25.50556257 41.68394495 28.14064038 52.69160883 4.22606766 17.37162448-37.73630017 42.14135425-72.50938081 72.80769204-103.21549295 2.18761121 3.04276886 4.15646224 6.24463696 6.40373557 9.22774369a1871.4375 1871.4375 0 0 0 140.04691725 5.34970492 1866.36093723 1866.36093723 0 0 0 140.04691723-5.34970492c2.24727335-2.98310674 4.21612437-6.18497483 6.3937923-9.2178004 30.66633723 30.70611158 55.4360664 65.4791928 72.80769147 103.21549355 11.00766384 23.91457269 46.15860503 21.27949489 52.69160879-4.22606768a371.15156223 371.15156223 0 0 0 11.514792-91.99901164c0-69.36717486-19.13165746-133.82216804-51.75690182-187.92578088-3.42062944-5.66790279-4.76302748-12.26056868-3.76865837-18.80351632a462.20625001 462.20625001 0 0 0 5.36959269-69.943909c-0.00994388-166.08943902-87.32547796-311.81420293-218.6915546-394.09823051zM605.93803103 357.87693858a93.93749974 93.93749974 0 1 1-187.89594924 6.1e-7 93.93749974 93.93749974 0 0 1 187.89594924-6.1e-7z" p-id="5405" data-v-c6073ba8></path><path d="M429.50777625 765.63860547C429.50777625 803.39355007 466.44236686 1000.39046097 512.00932183 1000.39046097c45.56695499 0 82.4922232-197.00623328 82.5015456-234.7518555 0-37.75494459-36.9345906-68.35043303-82.4922232-68.34111062-45.57627738-0.00932239-82.52019037 30.59548842-82.51086798 68.34111062z" p-id="5406" data-v-c6073ba8></path></svg></div></div></div>
    <script src="/blog/assets/js/app.0c46c7d8.js" defer></script><script src="/blog/assets/js/3.5679fe00.js" defer></script><script src="/blog/assets/js/1.8cdd2163.js" defer></script><script src="/blog/assets/js/17.d65c6ccd.js" defer></script>
  </body>
</html>
